<?php

class Admin_AnaliselistasimuladoController extends App_Controller_Action
{
    private $_table     = 'PUBLICACAO_SIMULADO';
    private $_pk        = 'PLS_ID';
    private $_order_col = 'PLS_DATENC DESC, PLS_HORENC DESC, PLS_ID';
    private $_order     = 'DESC';
    private $_sta_col   = 'PLS_STA';
    private $obj        = '';
    private $form       = '';
    
    
    public function init()
    {
        if($this->getRequest()->getActionName() == 'edit') $this->form = new Admin_Form_Publicacaosimulado();        
    }

    public function indexAction() {
        
        Zend_Loader::loadClass('tools','library');
        Zend_Loader::loadClass('datetools','library');
        Zend_Loader::loadClass('dominio','library');
        
        $where = ' PLS_ID > 0';
        
        $nmfiltro = $this->_getParam('nmfiltro', '');
        $dtfiltro = $this->_getParam('dtfiltro', '');
        
        if($nmfiltro != '') $where .= ' AND PLS_NOM like "%' . trim($nmfiltro) .'%"';
        if($dtfiltro != '') $where .= ' AND PLS_DATENC = "' . Datetools::dma_amd($dtfiltro) .'"';
        
        $this->view->nmfiltro = $nmfiltro;
        $this->view->dtfiltro = $dtfiltro;
        
        $query = 'SELECT SQL_CACHE * FROM '.$this->_table.' WHERE '.$where.' ORDER BY '.$this->_order_col.' '.$this->_order;
        $result = $this->db->fetchAll($query);
        $this->view->result = $this->paginacao($result);
             
    }
    
    public function editAction() {
        
        Zend_Loader::loadClass('tools','library');
        Zend_Loader::loadClass('datetools','library');
        Zend_Loader::loadClass('dominio','library');
        
        $this->view->headLink()->prependStylesheet($this->_urlbase.'/public/media/js/chosen/chosen.min.css'); 
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/chosen/chosen.jquery.min.js');
        $this->view->headScript()->appendFile($this->_urlbase.'/public/media/js/view/s_analiselistanap.js');
        
        
       
        $id = $this->_getParam('id', 0);
        if ($id > 0) {
            $data = $this->db->fetchRow('SELECT SQL_CACHE * FROM '.$this->_table.' WHERE '.$this->_pk . ' = ' . $id);
            //$this->form->populate($data);
            $this->view->data = $data;
        }
        
        $where = ' a.ALU_ID > 0';
        
        $ALU_ID = $this->_getParam('ALU_ID', 0);
        $CUR_ID = $this->_getParam('CUR_ID', 0);
        $CBS_ID = $this->_getParam('CBS_ID', 0);
        
        $this->view->ALU_ID = $ALU_ID;
        $this->view->CUR_ID = $CUR_ID;
        $this->view->CBS_ID = $CBS_ID;
        $this->view->buscar = $this->_getParam('buscar', '');
        
        if($ALU_ID > 0){
            /*
            // questões       
            $query = 'SELECT SQL_CACHE p.*,d.DCP_NOM  ' 
                    .' FROM PUBSIM_QSTDISALU p, DISCIPLINA d '
                    .' WHERE p.DCP_ID = d.DCP_ID AND p.PLS_ID = '.$id
                    .' AND ALU_ID = '.$ALU_ID
                    .' ORDER BY d.DCP_NOM, p.PQD_ACR DESC, p.DCP_ID, p.PLS_ID';
            $this->view->disc = $this->db->fetchAll($query);
            */
            
            // aluno
            $query = 'SELECT SQL_CACHE p.*,a.ALU_NOM  ' 
                    .' FROM PUBSIM_RANALU p, ALUNO a '
                    .' WHERE p.ALU_ID = a.ALU_ID AND p.PLS_ID = '.$id
                    .' AND p.ALU_ID = '.$ALU_ID
                    .' ORDER BY p.PLA_ACR DESC, p.ALU_ID, p.PLS_ID';
            $this->view->alunos = $this->db->fetchAll($query);
  
            // alunos não responderam
            $query = 'SELECT SQL_CACHE a.ALU_NOM  ' 
                    .' FROM PUBLICACAO_SIMULADO_ALUNO p, ALUNO a '
                    .' WHERE p.ALU_ID = a.ALU_ID AND p.ALU_ID = '.$ALU_ID
                    .' AND p.PLS_ID = '.$id.' AND p.PSA_STA = 0'
                    .' ORDER BY a.ALU_NOM ASC';
            $this->view->nad_alu = $this->db->fetchAll($query);
            
        }else if($CUR_ID > 0){
            
            $where .= ' AND c.CUR_ID = '.$CUR_ID;
            
            $list = '0';
            $query = "SELECT SQL_CACHE distinct(a.ALU_ID)
                FROM ALUNO as a
                INNER JOIN MATRICULA as m ON m.ALU_ID = a.ALU_ID AND m.MAT_STA = 1 
                INNER JOIN CURSO as c ON m.CUR_ID = c.CUR_ID 
                WHERE c.CUR_ID = ".$CUR_ID;
            $result = $this->db->fetchAll($query);
            foreach($result as $row){
                $list .= ','.$row['ALU_ID'];
            }
            
            /*
            // questões        
            $query = 'SELECT SQL_CACHE p.*,d.DCP_NOM  ' 
                    .' FROM PUBSIM_QSTDISCUR p, DISCIPLINA d '
                    .' WHERE p.DCP_ID = d.DCP_ID AND p.PLS_ID = '.$id
                    .' AND CUR_ID = '.$CUR_ID
                    .' ORDER BY d.DCP_NOM, p.PQD_ACR DESC, p.DCP_ID, p.PLS_ID';
            $this->view->disc = $this->db->fetchAll($query);
            */
            
            // aluno
            $query = 'SELECT SQL_CACHE p.*,a.ALU_NOM  ' 
                    .' FROM PUBSIM_RANALU p, ALUNO a '
                    .' WHERE p.ALU_ID = a.ALU_ID AND p.ALU_ID in('.$list.')'
                    .' AND p.PLS_ID = '.$id
                    .' ORDER BY p.PLA_ACR DESC, p.ALU_ID, p.PLS_ID';
            $this->view->alunos = $this->db->fetchAll($query);
            
            // alunos não responderam
            $query = 'SELECT SQL_CACHE a.ALU_NOM  ' 
                    .' FROM PUBLICACAO_SIMULADO_ALUNO p, ALUNO a '
                    .' WHERE p.ALU_ID = a.ALU_ID AND p.ALU_ID in('.$list.')'
                    .' AND p.PLS_ID = '.$id.' AND p.PSA_STA = 0'
                    .' ORDER BY a.ALU_NOM ASC';
            $this->view->nad_alu = $this->db->fetchAll($query);
        }else if($CBS_ID > 0){
            
            $where .= ' AND c.CBS_ID = '.$CBS_ID;
            
            $list = '0';
            $query = "SELECT SQL_CACHE distinct(a.ALU_ID)
                FROM ALUNO as a
                INNER JOIN MATRICULA as m ON m.ALU_ID = a.ALU_ID AND m.MAT_STA = 1 
                INNER JOIN CURSO as c ON m.CUR_ID = c.CUR_ID 
                WHERE c.CBS_ID = ".$CBS_ID;
            $result = $this->db->fetchAll($query);
            foreach($result as $row){
                $list .= ','.$row['ALU_ID'];
            }
            
            /*
            // questões        
            $query = 'SELECT SQL_CACHE p.*,d.DCP_NOM  ' 
                    .' FROM PUBSIM_QSTDISCBS p, DISCIPLINA d '
                    .' WHERE p.DCP_ID = d.DCP_ID AND p.PLS_ID = '.$id
                    .' AND CBS_ID = '.$CBS_ID
                    .' ORDER BY d.DCP_NOM, p.PQD_ACR DESC, p.DCP_ID, p.PLS_ID';
            $this->view->disc = $this->db->fetchAll($query);
            */
            
            // aluno
            $query = 'SELECT SQL_CACHE p.*,a.ALU_NOM  ' 
                    .' FROM PUBSIM_RANALU p, ALUNO a '
                    .' WHERE p.ALU_ID = a.ALU_ID AND p.ALU_ID in('.$list.')'
                    .' AND p.PLS_ID = '.$id
                    .' ORDER BY p.PLA_ACR DESC, p.ALU_ID, p.PLS_ID';
            $this->view->alunos = $this->db->fetchAll($query);
            
            // alunos não responderam
            $query = 'SELECT SQL_CACHE a.ALU_NOM  ' 
                    .' FROM PUBLICACAO_SIMULADO_ALUNO p, ALUNO a '
                    .' WHERE p.ALU_ID = a.ALU_ID AND p.ALU_ID in('.$list.')'
                    .' AND p.PLS_ID = '.$id.' AND p.PSA_STA = 0'
                    .' ORDER BY a.ALU_NOM ASC';
            $this->view->nad_alu = $this->db->fetchAll($query);
            
        }else{
            /*
            // questões        
            $query = 'SELECT SQL_CACHE p.*,d.DCP_NOM  ' 
                    .' FROM PUBSIM_QSTDIS p, DISCIPLINA d '
                    .' WHERE p.DCP_ID = d.DCP_ID AND p.PLS_ID = '.$id
                    .' ORDER BY p.PQD_QST, p.PQD_ACR DESC, p.DCP_ID, p.PLS_ID';
            $this->view->disc = $this->db->fetchAll($query);
            */
            
            // aluno
            $query = 'SELECT SQL_CACHE p.*,a.ALU_NOM  ' 
                    .' FROM PUBSIM_RANALU p, ALUNO a '
                    .' WHERE p.ALU_ID = a.ALU_ID AND p.PLS_ID = '.$id
                    .' ORDER BY p.PLA_ACR DESC, p.ALU_ID, p.PLS_ID';
            $this->view->alunos = $this->db->fetchAll($query);
            
            // alunos não responderam
            $query = 'SELECT SQL_CACHE a.ALU_ID,a.ALU_NOM  ' 
                    .' FROM PUBLICACAO_SIMULADO_ALUNO p, ALUNO a '
                    .' WHERE p.ALU_ID = a.ALU_ID AND p.PSA_STA = 0'
                    .' AND p.PLS_ID = '.$id
                    .' ORDER BY a.ALU_NOM ASC';
            $this->view->nad_alu = $this->db->fetchAll($query);
        }
        
        
        $query = "SELECT SQL_CACHE distinct(a.ALU_ID), a.ALU_NOM
                FROM ALUNO as a
                LEFT JOIN MATRICULA as m ON m.ALU_ID = a.ALU_ID AND m.MAT_STA = 1 
                LEFT JOIN CURSO as c ON m.CUR_ID = c.CUR_ID 
                WHERE ".$where." ORDER BY a.ALU_NOM ASC";
        $result = $this->db->fetchAll($query);
        $this->view->result = $result;
        
        
    }

    public function deleteAction() {
        $this->_helper->layout->disableLayout(); 
        $this->_helper->viewRenderer->setNoRender();  
        $ok = FALSE;
        $msg = '';
        $id = $this->_getParam('id', 0);
        if((int) $id > 0 ) {   
            $this->db->beginTransaction();
            try {
                $ok = (bool) $this->db->delete($this->_table, $this->_pk . ' = ' . $id);
                $this->db->commit();
            } catch(Exception $ex) {
                //echo getMessage();
                $ok = FALSE;
                $msg = MSG_DELETE;
                $this->db->rollBack();
            }             
        }       
        echo json_encode(array('ok' => $ok, 'msg'=> $msg));
        //$this->_helper->redirector('index');
    }
    
    public function comboalunosAction(){
        $this->_helper->layout->disableLayout(); 
        $this->_helper->viewRenderer->setNoRender();  
        $ok = TRUE;
        $html = '';
        
        $where = ' a.ALU_ID > 0';
        
        $cur_id = $this->_getParam('cur_id', 0);
        if((int) $cur_id > 0 ) $where .= " and c.CUR_ID = ".$cur_id;
               
        $query = "SELECT SQL_CACHE distinct(a.ALU_ID), a.ALU_NOM
        FROM ALUNO as a
        LEFT JOIN MATRICULA as m ON m.ALU_ID = a.ALU_ID  
        LEFT JOIN CURSO as c ON m.CUR_ID = c.CUR_ID 
        WHERE ".$where." ORDER BY a.ALU_NOM ASC";

        $result = $this->db->fetchAll($query);


        $html = '<select id="ALU_ID" name="ALU_ID" class="chosen-select" >';
        $html .= '<option value="">Todos Alunos</option>';

        foreach ($result as $r) {
            $html .= '<option '.$select.' value="'.$r['ALU_ID'].'">'.$r['ALU_NOM'].'</option>'; // $r['CUR_NOM'] 
        }

        $html .= '</select><script> $(function(){ $(".chosen-select").chosen(); }); </script>';

        echo json_encode(array('ok' => $ok,'html'=>$html));
    }

    public function comboturmasAction(){
        $this->_helper->layout->disableLayout(); 
        $this->_helper->viewRenderer->setNoRender();  
        $ok = TRUE;
        $html = '';
        $html2 = '';
        
        Zend_Loader::loadClass('dominio','library');
        
        // turmas
        $where = 'CUR_ID > 0 ';
        
        $cbs_id = $this->_getParam('cbs_id', 0);
        if((int) $cbs_id > 0 ) $where .= ' and CBS_ID = '.$cbs_id;
              
            
        $query = 'SELECT SQL_CACHE CUR_ID, CUR_NOM FROM CURSO WHERE '.$where.' and CUR_STA = 1 and CUR_ANO >= '.date('Y');

        $result = $this->db->fetchAll($query);


        $html = '<select id="CUR_ID" name="CUR_ID" class="chosen-select" style="width: 200px !important;" >';
        $html .= '<option value="">Todos Turmas</option>';
        foreach($result as $r){   
            $select = '';
            $html .= '<option '.$select.' value="'.$r['CUR_ID'].'">'.dominio::getTurma($r['CUR_ID']).'</option>'; // $r['CUR_NOM'] 
        }
        $html .= '</select><script> $(function(){ $(".chosen-select").chosen(); }); </script>';
        
        // alunos
        
        $where = ' a.ALU_ID > 0';
        
        if((int) $cbs_id > 0 ) $where .= " and c.CBS_ID = ".$cbs_id;
 
        $query = "SELECT SQL_CACHE distinct(a.ALU_ID), a.ALU_NOM
        FROM ALUNO as a
        LEFT JOIN MATRICULA as m ON m.ALU_ID = a.ALU_ID  
        LEFT JOIN CURSO as c ON m.CUR_ID = c.CUR_ID 
        WHERE ".$where." ORDER BY a.ALU_NOM ASC";

        $result = $this->db->fetchAll($query);


        $html2 = '<select id="ALU_ID" name="ALU_ID" class="chosen-select" >';
        $html2 .= '<option value="">Todos Alunos</option>';

        foreach ($result as $r) {
            $html2 .= '<option '.$select.' value="'.$r['ALU_ID'].'">'.$r['ALU_NOM'].'</option>'; // $r['CUR_NOM'] 
        }

        $html2 .= '</select><script> $(function(){ $(".chosen-select").chosen(); }); </script>';
        echo json_encode(array('ok' => $ok,'html'=>$html,'html2'=>$html2));
    }
    
    public function relatorioAction(){
        
        $this->_helper->layout->disableLayout(); 
        
        Zend_Loader::loadClass('tools','library');
        Zend_Loader::loadClass('datetools','library');
        Zend_Loader::loadClass('dominio','library');
        
        $id= $this->_getParam('id', 0);
        $alu_id= $this->_getParam('alu_id', 0);
        
        $this->view->PLS_ID = $id;
        $this->view->ALU_ID = $alu_id;
        
        $query = 'SELECT SQL_CACHE p.*,d.DCP_NOM, d.DCP_NOMABV ' 
                .' FROM PUBLICACAO_SIMULADO_GABARITO p, DISCIPLINA d '
                .' WHERE p.DCP_ID = d.DCP_ID AND p.PLS_ID = '.$id
                .' GROUP BY p.DCP_ID ASC'
                .' ORDER BY p.PSG_QST ASC';
        $this->view->result = $this->db->fetchAll($query);
        
        $query = "SELECT SQL_CACHE distinct(a.ALU_ID), a.ALU_NOM,c.CUR_ID
                    FROM ALUNO as a
                    INNER JOIN MATRICULA as m ON m.ALU_ID = a.ALU_ID AND m.MAT_STA = 1 
                    INNER JOIN CURSO as c ON m.CUR_ID = c.CUR_ID
                    WHERE a.ALU_ID = ".$alu_id;
        $this->view->aluno  = $this->db->fetchRow($query);
        
        $query = "SELECT SQL_CACHE * FROM PUBLICACAO_SIMULADO WHERE PLS_ID = ".$id;
        $this->view->simulado  = $this->db->fetchRow($query);
        
        /*
        $query = 'SELECT SQL_CACHE p.*, d.DCP_NOM, d.DCP_NOMABV  '
                .' FROM PUBSIM_RANALU p, DISCIPLINA d '
                .' WHERE p.PLS_ID = ' . $id
                .' AND p.PLA_REDID = d.DCP_ID '
                .' ORDER BY PLA_POR DESC';
        */
        $query = 'SELECT SQL_CACHE p.*, d.DCP_NOM, d.DCP_NOMABV  '
                .' FROM PUBSIM_RANALU p'
                .' LEFT JOIN DISCIPLINA as d ON p.PLA_REDID = d.DCP_ID '
                .' WHERE p.PLS_ID = ' . $id
                .' ORDER BY PLA_POR DESC';
        $this->view->mel = $this->db->fetchRow($query);
    }
}